// historypage.cpp
#include "historypage.h"
#include <QtSql>
#include <QMessageBox>
#include <QVBoxLayout>
#include <QHBoxLayout>
#include <QHeaderView>

HistoryPage::HistoryPage(QWidget *parent) : QWidget(parent)
{
    // Check_Database();
    // setupModel();
}

void HistoryPage::Check_Database()
{
    // bool exists=db_manager->tableExists()
}
void HistoryPage::insert_Data(QList<Receive_Processor*>*receive_processors,int index)
{
    DatabaseManager &instance=DatabaseManager::instance();
    QSqlQuery *query;
    //获取数据头
    QString address=receive_processors->value(index)->address;

    bool table_exists=instance.tableExists(address);// tableExits()->QsqlQuery->hasnext
    // qDebug()<<"表格"<<data_head<<"存在性:"<<table_exists;

    //通过数据头判断数据表格是否存在
    if (table_exists==false)
    {   //默认的表为user
        // Create table if not exists
        // 如果表格不存在，通过配置文件创建该表格
        QString create_table_str="CREATE TABLE IF NOT EXISTS"+QString(" ")+'"'+address+'"'+" ("
            "id INTEGER PRIMARY KEY AUTOINCREMENT,Recorded_Time DATETIME NOT NULL";
        for(int param_index=0;param_index<receive_processors->value(index)->real_value_list->count();param_index++)
        {
            create_table_str+=",";
            create_table_str+=receive_processors->value(index)->param_db_name->value(param_index);
            create_table_str+=" ";
            create_table_str+=receive_processors->value(index)->db_type_list->value(param_index);

        }
        create_table_str.replace("\n","");
        create_table_str.replace("\r"," ,");
        create_table_str.replace('\\',"");
        create_table_str.append(")");
        // qDebug()<<create_table_str;
        query= instance.executeQuery(create_table_str);
        table_exists=instance.tableExists(address);
        if (!table_exists) {
            QMessageBox::warning(nullptr,"提示","未能成功创建数据表，请重新尝试\n"+query->lastError().text(),QMessageBox::Ok);
            qCritical() << "Create table failed:" << query->lastError().text();
        }
    }

    //将处理器对应的数据插入到对应的表中
    QString query_insert_str=QString("INSERT INTO ")+'"'+address+'"'+" (Recorded_Time";
    QVariantMap insert_map;
    for(int param_index=0;param_index<receive_processors->value(index)->real_value_list->count();param_index++)
    {
        query_insert_str+=",";
        query_insert_str+=receive_processors->value(index)->param_db_name->value(param_index);
        // query_insert_str+=" ";
    }


    query_insert_str+=") VALUES(";
    query_insert_str+=":Recorded_Time,";
    insert_map.insert("Recorded_Time",QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss"));//插入第一个的位置为当前的时间
    for(int param_index=0;param_index<receive_processors->value(index)->real_value_list->count();param_index++)
    {
        query_insert_str+=":"+receive_processors->value(index)->param_db_name->value(param_index)+",";
        insert_map.insert(receive_processors->value(index)->param_db_name->value(param_index),receive_processors->value(index)->real_value_list->value(param_index));
    }
    query_insert_str.removeAt(query_insert_str.size()-1);//去除","
    query_insert_str+=")";
    query=instance.executeQuery(query_insert_str,insert_map);
}

void HistoryPage::setupUI(QList<Receive_Processor*>*receive_processors)
{
    DatabaseManager&instance=DatabaseManager::instance();
    instance.init();

    model=new QSqlTableModel(nullptr,instance.get_db());

    param_item_list=new QList<QListWidgetItem*>;
    param_select_list=new QListWidget;

    qDebug()<<"setupUI";
    // Create widgets
    startTimeEdit = new QDateTimeEdit(QDateTime::currentDateTime().addSecs(-3600*24));
    endTimeEdit = new QDateTimeEdit(QDateTime::currentDateTime());
    device_address=new QList<QString>;

    tableView = new QTableView();
    // tableView->setSelectionModel(theSelection);//用于定义视图(View)选择哪种选择行为
    tableView->setSelectionBehavior(QAbstractItemView::SelectRows);//用于定义视图（view）的选择模式
    tableView->setAlternatingRowColors(true);//用来控制当前是否启用交替行颜色绘制背景
    searchButton = new QPushButton("重新搜索并显示");
    deleteButton = new QPushButton("删除选择项");
    clearButton = new QPushButton("删除所有显示项目");
    // testDataButton = new QPushButton("添加测试数据");
    export_to_data_analysis_button=new QPushButton("将选中项目用于数据分析");
    export_to_csv=new QPushButton("导出为CSV文件");
    export_to_html=new QPushButton("导出为HTML文件");
    export_to_excel=new QPushButton("导出为Excel文件");//需要客户端安装EXCEL软件！！
    connect(this->export_to_csv,SIGNAL(pressed()),this,SLOT(exportSelectedToCSV()));
    connect(this->export_to_html,SIGNAL(pressed()),this,SLOT(exportToExcel()));
    connect(this->export_to_excel,SIGNAL(pressed()),this,SLOT(exportSelectedToExcel()));
    connect(this->export_to_data_analysis_button,SIGNAL(pressed()),this,SLOT(export_to_data_analysis()));
    //设备选择项
    device_combo=new QComboBox();
    for(int device_index=0;device_index<receive_processors->count();device_index++)
    {
        QString address=receive_processors->value(device_index)->address;
        //获取数据头
        device_combo->addItem(address);
        device_address->push_back(address);

        bool table_exists=instance.tableExists(address);// tableExits()->QsqlQuery->hasnext
        qDebug()<<"表格"<<address<<"存在性:"<<table_exists;

        //通过数据头判断数据表格是否存在
        if (table_exists==false)
        {   //默认的表为user
            // Create table if not exists
            // 如果表格不存在，通过配置文件创建该表格
            QString create_table_str="CREATE TABLE IF NOT EXISTS"+QString(" ")+'"'+address+'"'+" ("
            "id INTEGER PRIMARY KEY AUTOINCREMENT,Recorded_Time DATETIME NOT NULL";
            for(int param_index=0;param_index<receive_processors->value(device_index)->param_db_name->count();param_index++)
            {
                create_table_str+=",";
                create_table_str+=receive_processors->value(device_index)->param_db_name->value(param_index);
                create_table_str+=" ";
                create_table_str+=receive_processors->value(device_index)->db_type_list->value(param_index);

            }
            create_table_str.replace("\n","");
            create_table_str.replace("\r"," ,");
            create_table_str.replace('\\',"");
            create_table_str.append(")");
            qDebug()<<create_table_str;
            QSqlQuery *query= instance.executeQuery(create_table_str);
            table_exists=instance.tableExists(address);
            if (!table_exists) {
                QMessageBox::warning(nullptr,"提示","未能成功创建数据表，请重新尝试\n"+query->lastError().text(),QMessageBox::Ok);
                qCritical() << "Create table failed:" << query->lastError().text();
            }
        }

    }
    //连接设备选择框的更换设备事件
    connect(device_combo,SIGNAL(currentTextChanged(QString)),this,SLOT(change_device_table(QString)));


    //设备的参数筛选项
    if(device_combo->count()>0)
    {
        //默认设置第一个设备的数据为默认打开页面的数据
        device_combo->setCurrentIndex(0);

        //能耗参数筛选项
        typeCombo = new QComboBox();
        typeCombo->addItem(QString("全部"));
        for(int param_index=0;param_index<receive_processors->value(0)->annotation_list->count();param_index++)
        {
            typeCombo->addItem(receive_processors->value(0)->annotation_list->value(param_index));
        }

        //默认选中全部参数
        typeCombo->setCurrentIndex(0);
        model->setTable(device_address->value(0)); //设置数据表
        model->setEditStrategy(QSqlTableModel::OnManualSubmit);//数据保存方式，OnManualSubmit , OnRowChange
        if (!(model->select()))//查询数据
        {
            QMessageBox::critical(this, "错误信息",
                                  "打开数据表错误,错误信息\n"+model->lastError().text(),
                                  QMessageBox::Ok,QMessageBox::NoButton);
            return;
        }
        model->setSort(model->fieldIndex("id"),Qt::AscendingOrder); //排序
        tableView->setModel(model);
        tableView->hideColumn(0); // Hide ID column
        model->setHeaderData(model->fieldIndex("Recoreded_Time"),Qt::Horizontal,"记录时间");
        theSelection=tableView->selectionModel();
        //theSelection当前项变化时触发currentChanged信号
        connect(theSelection,SIGNAL(currentChanged(QModelIndex,QModelIndex)),
                this,SLOT(on_currentChanged(QModelIndex,QModelIndex)));
        //选择行变化时
        connect(theSelection,SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
                this,SLOT(on_currentRowChanged(QModelIndex,QModelIndex)));
        for(int param_index=0;param_index<receive_processors->value(0)->annotation_list->count();param_index++)
        {
            model->setHeaderData(model->fieldIndex(receive_processors->value(0)->param_db_name->value(param_index)),Qt::Horizontal,receive_processors->value(0)->annotation_list->value(param_index));
            //添加筛选列表的选项
            QListWidgetItem *param_list_item=new QListWidgetItem(receive_processors->value(0)->annotation_list->value(param_index));
            param_item_list->push_back(param_list_item);
            param_select_list->addItem(param_list_item);
            // param_list_item->setSelected(true);//默认选中
            param_list_item->setCheckState(Qt::Checked);
        }

    }
    qDebug()<<"setupUI3";


    // Layout
    QHBoxLayout *filterLayout = new QHBoxLayout();
    filterLayout->setAlignment(Qt::AlignLeft);
    QLabel *start_time_label=new QLabel("起始时间:");
    QLabel *end_time_label=new QLabel("截止时间:");
    filterLayout->addWidget(start_time_label);
    filterLayout->addWidget(startTimeEdit);
    startTimeEdit->setAlignment(Qt::AlignLeft);
    filterLayout->addWidget(end_time_label);
    filterLayout->addWidget(endTimeEdit);
    endTimeEdit->setAlignment(Qt::AlignLeft);
    QLabel *device_select_label=new QLabel("数据库表(设备地址_操作码_有效位数):");
    filterLayout->addWidget(device_select_label);

    filterLayout->addWidget(device_combo);
    // filterLayout->addWidget(typeCombo);
    filterLayout->addWidget(searchButton);
    // qDebug()<<"setupUI4";
    QVBoxLayout *buttonLayout = new QVBoxLayout();
    buttonLayout->setAlignment(Qt::AlignTop);
    buttonLayout->setSpacing(10);
    QLabel * param_select_list_label=new QLabel("筛选显示的列选项");
    buttonLayout->addWidget(param_select_list_label);

    QHBoxLayout *select_hlayout=new QHBoxLayout;
    QPushButton *select_all=new QPushButton("全选");
    QPushButton *un_select_all=new QPushButton("全不选");
    select_hlayout->addWidget(select_all);
    select_hlayout->addWidget(un_select_all);
    select_hlayout->setAlignment(Qt::AlignLeft);
    select_hlayout->setSpacing(2);
    connect(select_all,SIGNAL(pressed()),this,SLOT(select_all()));
    connect(un_select_all,SIGNAL(pressed()),this,SLOT(select_not_all()));

    buttonLayout->addLayout(select_hlayout);
    buttonLayout->addWidget(param_select_list);//添加筛选列表


    buttonLayout->addWidget(deleteButton);
    buttonLayout->addWidget(clearButton);
    // buttonLayout->addWidget(testDataButton);
    buttonLayout->addWidget(export_to_data_analysis_button);
    buttonLayout->addWidget(export_to_csv);
    buttonLayout->addWidget(export_to_html);
    buttonLayout->addWidget(export_to_excel);
    QVBoxLayout *mainLayout = new QVBoxLayout(this);
    QHBoxLayout *second_layout=new QHBoxLayout(nullptr);
    second_layout->addWidget(tableView,6);
    second_layout->addLayout(buttonLayout,1);
    mainLayout->addLayout(filterLayout);
    mainLayout->addLayout(second_layout);
    // qDebug()<<"setupUI5";
    // Connections
    connect(searchButton, &QPushButton::clicked, this, &HistoryPage::onSearch);
    connect(deleteButton, &QPushButton::clicked, this, &HistoryPage::onDelete);
    connect(clearButton, &QPushButton::clicked, this, &HistoryPage::onClear);
    // connect(testDataButton, &QPushButton::clicked, this, &HistoryPage::onAddTestData);
    qDebug()<<"历史查询页面初始化完毕";
}

void HistoryPage::change_device_table(QString address)//更换当前显示的设备的数据表
{
    model->setTable(address);
}

void HistoryPage::select_all()
{
    for(int select_index=0;select_index<param_item_list->count();select_index++)
    {
        param_item_list->value(select_index)->setCheckState(Qt::Checked);
    }
};

void HistoryPage::select_not_all()
{
    for(int select_index=0;select_index<param_item_list->count();select_index++)
    {
        param_item_list->value(select_index)->setCheckState(Qt::Unchecked);
    }
}

void HistoryPage::on_currentChanged(const QModelIndex &current, const QModelIndex &previous)
{
    //更新actPost和actCancel 的状态
    // Q_UNUSED(current);
    // Q_UNUSED(previous);
    // ui->actSubmit->setEnabled(model->isDirty()); //有未保存修改时可用
    // ui->actRevert->setEnabled(model->isDirty());
}

void HistoryPage::on_currentRowChanged(const QModelIndex &current, const QModelIndex &previous)
{
    // Q_UNUSED(previous);
    // // 行切换时的状态控制
    // ui->actRecDelete->setEnabled(current.isValid());
    // ui->actPhoto->setEnabled(current.isValid());
    // ui->actPhotoClear->setEnabled(current.isValid());

    // if (!current.isValid())
    // {
    //     ui->dbLabPhoto->clear(); //清除图片显示
    //     return;
    // }

    // dataMapper->setCurrentIndex(current.row()); //更新数据映射到当前行，使界面上的编辑框、下拉列表框等与字段关联的界面组件显示当前记录的内容

    // int curRecNo=current.row();//获取行号
    // QSqlRecord  curRec=this->model->record(curRecNo); //获取当前记录

    // if (curRec.isNull("Photo"))  //图片字段内容为空
    //     ui->dbLabPhoto->clear();
    // else
    // {
    //     QByteArray data=curRec.value("Photo").toByteArray();
    //     QPixmap pic;
    //     pic.loadFromData(data);
    //     ui->dbLabPhoto->setPixmap(pic.scaledToWidth(ui->dbLabPhoto->size().width()));
    // }
}

void HistoryPage::onSearch()
{

    QString typeFilter = typeCombo->currentText();
    QString filter = QString("Recorded_Time BETWEEN '%1' AND '%2'")
                         .arg(startTimeEdit->dateTime().toString("yyyy-MM-dd HH:mm:ss"))
                         .arg(endTimeEdit->dateTime().toString("yyyy-MM-dd HH:mm:ss"));

    //总数隐藏id列，所以i+1
    for (int i = 0; i < param_item_list->count(); ++i) {
        if (param_item_list->value(i)->checkState()==Qt::Checked) {

            this->tableView->showColumn(i+2);//显示该列
        }else if(param_item_list->value(i)->checkState()==Qt::Unchecked)
        {
            this->tableView->hideColumn(i+2);  // 隐藏该列
        }
    }
    qDebug()<<"设置过滤器成功";
    model->setFilter(filter);
    model->select();
}

void HistoryPage::onDelete()
{
    QModelIndexList selected = theSelection->selectedRows();
    if (selected.isEmpty()) {
        QMessageBox::warning(this, "警告", "请先选中项目，再进行删除");
        return;
    }

    if (QMessageBox::question(this, "删除确认", "确认删除选中项?") == QMessageBox::Yes) {
        model->removeRow(selected.first().row());
        model->submitAll();
    }
}

void HistoryPage::onClear()
{
    if (QMessageBox::question(this, "Confirm", "清空并删除所有记录？该地址设备的所有接收记录都会被删除！！！") == QMessageBox::Yes) {
        model->removeRows(0, model->rowCount());
        model->submitAll();
        model->select();
    }
}

///将选中数据用于数据分析
void HistoryPage::export_to_data_analysis()
{
    QMessageBox::information(this, "提示", "正在开发中......");
    //得到选中的数据
    //转到数据分析面板
}

///导出历史数据到CSV文件
void HistoryPage::exportSelectedToCSV() {
    QModelIndexList selectedRows = theSelection->selectedRows();
    if (selectedRows.isEmpty()) {
        QMessageBox::warning(this, "警告", "请先选择要导出的行");
        return;
    }

    QString fileName = QFileDialog::getSaveFileName(this, "导出CSV", "", "CSV Files (*.csv)");
    if (fileName.isEmpty()) return;

    QFile file(fileName);
    if (!file.open(QIODevice::WriteOnly | QIODevice::Text)) {
        QMessageBox::critical(this, "错误", "无法打开文件");
        return;
    }

    QTextStream out(&file);
    // 写入表头（保持原逻辑）
    for (int col = 0; col < this->model->columnCount(); ++col) {
        if (col > 0) out << ",";
        out << this->model->headerData(col, Qt::Horizontal).toString();
    }
    out << "\n";

    // 遍历选中行
    for (const QModelIndex &index : selectedRows) {
        int row = index.row();
        for (int col = 0; col < this->model->columnCount(); ++col) {
            if (col > 0) out << ",";
            QVariant value = this->model->data(this->model->index(row, col));
            out << value.toString().replace("\"", "\"\"");
        }
        out << "\n";
    }

    file.close();
    QMessageBox::information(this, "完成", "已导出选中行到CSV");
}

///导出HTML文件（伪excel）
void HistoryPage::exportToExcel() {
    QModelIndexList selectedRows = theSelection->selectedRows();
    QString fileName = QFileDialog::getSaveFileName(this, "导出Excel", "", "HTML Files (*.html)");
    if (fileName.isEmpty()) return;

    QFile file(fileName);
    if (!file.open(QIODevice::WriteOnly | QIODevice::Text)) {
        QMessageBox::critical(this, "错误", "无法打开文件");
        return;
    }

    QTextStream out(&file);
    out << "<html><body><table border='1'>\n";

    // 表头
    out << "<tr>";
    for (int col = 0; col < this->tableView->model()->columnCount(); ++col) {
        out << "<th>" << this->tableView->model()->headerData(col, Qt::Horizontal).toString() << "</th>";
    }
    out << "</tr>\n";

    // 数据行
    for (const QModelIndex &index : selectedRows) {
        int row = index.row();
        out << "<tr>";
        for (int col = 0; col < model->columnCount(); ++col) {
            out << "<td>" << model->data(model->index(row, col)).toString() << "</td>";
        }
        out << "</tr>\n";
    }

    out << "</table></body></html>";
    file.close();
    QMessageBox::information(this, "完成", "HTML表格已生成（可用Excel打开）");
}


///导出原生EXCEL文件
void HistoryPage::exportSelectedToExcel() {
    // 获取选中行索引列表
    QModelIndexList selectedRows = theSelection->selectedRows();
    if (selectedRows.isEmpty()) {
        QMessageBox::warning(this, "警告", "请先选择要导出的行");
        return;
    }

    // 选择保存路径
    QString fileName = QFileDialog::getSaveFileName(this, "导出Excel", "", "Excel Files (*.xlsx)");
    if (fileName.isEmpty()) return;

    // 创建Excel实例
    QAxObject* excel = new QAxObject("Excel.Application");
    excel->setProperty("Visible", false); // 后台运行
    QAxObject* workbooks = excel->querySubObject("Workbooks");
    QAxObject* workbook = workbooks->querySubObject("Add");
    QAxObject* worksheet = workbook->querySubObject("ActiveSheet");

    // 写入表头
    for (int col = 0; col < model->columnCount(); ++col) {
        QAxObject* cell = worksheet->querySubObject("Cells(int,int)", 1, col + 1);
        cell->setProperty("Value", model->headerData(col, Qt::Horizontal).toString());
        delete cell;
    }

    // 遍历选中行并写入数据
    int excelRow = 2; // Excel数据行从第2行开始（表头在第1行）
    for (const QModelIndex &index : selectedRows) {
        int modelRow = index.row();
        for (int col = 0; col < model->columnCount(); ++col) {
            QAxObject* cell = worksheet->querySubObject("Cells(int,int)", excelRow, col + 1);
            QVariant data = model->data(model->index(modelRow, col));
            cell->setProperty("Value", data.toString());
            delete cell;
        }
        excelRow++;
    }

    // 保存并释放资源
    workbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(fileName));
    workbook->dynamicCall("Close()");
    excel->dynamicCall("Quit()");
    delete worksheet;
    delete workbook;
    delete workbooks;
    delete excel;

    QMessageBox::information(this, "完成", "已导出选中行到Excel");
}
